package kit.docker.excel;

import com.alibaba.excel.EasyExcel;
import com.alibaba.excel.ExcelWriter;
import com.alibaba.excel.write.builder.ExcelWriterBuilder;
import com.alibaba.excel.write.metadata.WriteSheet;
import com.alibaba.excel.write.metadata.fill.FillConfig;
import com.alibaba.excel.write.metadata.fill.FillWrapper;
import com.alibaba.excel.write.metadata.style.WriteCellStyle;
import com.alibaba.excel.write.metadata.style.WriteFont;
import com.alibaba.excel.write.style.HorizontalCellStyleStrategy;
import com.docker.core.mvc.Remark;
import com.jfinal.kit.StrKit;
import org.apache.commons.collections4.map.LinkedMap;
import org.apache.poi.ss.usermodel.IndexedColors;

import javax.servlet.ServletOutputStream;
import javax.servlet.http.HttpServletResponse;
import java.io.IOException;
import java.net.URLEncoder;
import java.util.ArrayList;
import java.util.List;
import java.util.Map;

public class AliExcelKit {

    //------------------------------   Excel表格导出  ------------------------------

    /**
     * @Param: [response http响应, fileName 文件名称, sheetName sheet名称, list 数据列表, clazz 指定用哪个class去写]
     * @return: void
     * @Author: ldl
     * @Create: 2020/06/25 025 11:55
     */
    @Remark("导出单个sheet并下载表格")
    public static void export(HttpServletResponse response, String fileName, String sheetName, List<?> list, Class clazz) throws IOException {
        write(response, fileName, sheetName, list, clazz, null);
    }

    /**
     * @Param: [response http响应, fileName 文件名称, sheetName sheet名称, list 数据列表, clazz 指定用哪个class去写，excludeColumns 过滤的参数列表]
     * @return: void
     * @Author: ldl
     * @Create: 2020/06/25 025 11:55
     */
    @Remark("导出单个sheet并下载表格，过滤指定参数")
    public static void export(HttpServletResponse response, String fileName, String sheetName, List<?> list, Class clazz, List<String> excludeColumns) throws IOException {
        write(response, fileName, sheetName, list, clazz, excludeColumns);
    }

    /**
     * @Param: [filePath 目标路径, sheetName sheet名称, list 数据列表, clazz 指定用哪个class去写]
     * @return: void
     * @Author: ldl
     * @Create: 2020/06/25 025 11:58
     */
    @Remark("单个sheet写到指定目录")
    public static void write(String filePath, String sheetName, List<?> list, Class clazz) throws IOException {
        write(null, filePath, sheetName, list, clazz, null);
    }

    /**
     * @Param: [filePath 目标路径, sheetName sheet名称, list 数据列表, clazz 指定用哪个class去写，excludeColumns 过滤的参数列表]
     * @return: void
     * @Author: ldl
     * @Create: 2020/06/25 025 11:58
     */
    @Remark("单个sheet写到指定目录,过滤指定参数")
    public static void write(String filePath, String sheetName, List<?> list, Class clazz, List<String> excludeColumns) throws IOException {
        write(null, filePath, sheetName, list, clazz, excludeColumns);
    }

    /**
     * @Param: [response http响应, fileName 文件名称, sheet 指定sheet名称和对应sheet用哪个class去写, list 与sheet对应的数据列表]
     * @return: void
     * @Author: ldl
     * @Create: 2020/06/25 025 11:55
     */
    @Remark("导出多个sheet并下载表格")
    public static void export2Sheet(HttpServletResponse response, String fileName, LinkedMap<String, Class> sheet, List<?>... datas) throws IOException {
        if (sheet.size() != datas.length) {
            throw new RuntimeException("数据格式错误");
        }
        ExcelWriter excelWriter = EasyExcel.write(getOutputStream(response, fileName))
                .registerWriteHandler(headStyleWrite(IndexedColors.SKY_BLUE, 12))
                .build();
        int index = 0;
        for (String key : sheet.keySet()) {
            WriteSheet writeSheet1 = EasyExcel.writerSheet(index, key).head(sheet.get(key)).build();
            excelWriter.write(datas[index], writeSheet1);
            index++;
        }
        excelWriter.finish();
    }

    private static void write(HttpServletResponse response, String fileName, String sheetName, List<?> list, Class clazz, List<String> excludeColumns) throws IOException {
        ExcelWriterBuilder excelWriterBuilder;
        if (StrKit.notNull(response)) {
            excelWriterBuilder = EasyExcel.write(getOutputStream(response, fileName), clazz);
        } else {
            excelWriterBuilder = EasyExcel.write(fileName, clazz);
        }
        excelWriterBuilder.registerWriteHandler(headStyleWrite(IndexedColors.SKY_BLUE, 12));
//        excelWriterBuilder.registerWriteHandler(new SelfCellWriteHandler());//操作指定的单元格
        if (StrKit.notNull(excludeColumns) && excludeColumns.size() > 0) {
            excelWriterBuilder.excludeColumnFiledNames(excludeColumns);
        }
        excelWriterBuilder.sheet(sheetName).doWrite(list);
    }

    //设置excel标题的字体颜色,大小
    private static HorizontalCellStyleStrategy headStyleWrite(IndexedColors colors, int points) {
        WriteCellStyle headWriteCellStyle = new WriteCellStyle();
        headWriteCellStyle.setFillForegroundColor(colors.getIndex());
        WriteFont headWriteFont = new WriteFont();
        headWriteFont.setFontHeightInPoints((short) points);
        headWriteCellStyle.setWriteFont(headWriteFont);
        return new HorizontalCellStyleStrategy(headWriteCellStyle, new WriteCellStyle());
    }

    private static ServletOutputStream getOutputStream(HttpServletResponse response, String fileName) throws IOException {
        // response.setContentType("application/vnd.ms-excel");
        response.setContentType("application/vnd.openxmlformats-officedocument.spreadsheetml.sheet");//可以在浏览器中查看表格内容
        response.setCharacterEncoding("utf-8");
        response.setHeader("Content-disposition", "attachment;filename=" + URLEncoder.encode(fileName, "UTF-8"));
        return response.getOutputStream();
    }

    /**
     * @Param: [templatePath 模板绝对路径, targetPath 目标绝对路径, list 数据列表]
     * @return: void
     * @Author: ldl
     * @Create: 2020/06/25 025 10:24
     */
    @Remark("根据模板导出表格到指定目录")
    public static void exportWithTemplate(String templatePath, String targetPath, List<?>... list) throws IOException {
        exportWithTemplate(null, templatePath, targetPath, null, list);
    }

    /**
     * @Param: [response http响应, templatePath 模板绝对路径, fileName 文件名称, list 数据列表]
     * @return: void
     * @Author: ldl
     * @Create: 2020/06/25 025 10:25
     */
    @Remark("根据模板导出并下载表格")
    public static void exportWithTemplate(HttpServletResponse response, String templatePath, String fileName, List<?>... list) throws IOException {
        exportWithTemplate(response, templatePath, fileName, null, list);
    }

    /**
     * @Param: [response http响应, templatePath 模板绝对路径, targetPath 目标文件, map 模板中填充指定字段的map集合, list 数据列表]
     * @return: void
     * @Author: ldl
     * @Create: 2020/06/24 025 15:28
     */
    @Remark("根据模板导出表格")
    public static void exportWithTemplate(HttpServletResponse response, String templatePath, String targetPath, Map<String, Object> map, List<?>... list) throws IOException {
        ExcelWriterBuilder writerBuilder;

        if (StrKit.notNull(response)) {
            //此时targetPath为含有格式的文件名称
            writerBuilder = EasyExcel.write(getOutputStream(response, targetPath));
        } else {
            //此时targetPath为含有格式的文件绝对路径
            writerBuilder = EasyExcel.write(targetPath);
        }
        ExcelWriter excelWriter = writerBuilder.withTemplate(templatePath).build();

        WriteSheet writeSheet = EasyExcel.writerSheet().build();
        // 如果模板有list,且list不是最后一行，下面还有数据需要填充 就必须设置 forceNewRow=true
        // 但是这个就会把所有数据放到内存,会很耗内存
        FillConfig.FillConfigBuilder fillConfigBuilder = FillConfig.builder();
        FillConfig fillConfig = fillConfigBuilder.forceNewRow(Boolean.TRUE).build();
//        fillConfigBuilder.direction(WriteDirectionEnum.HORIZONTAL).build();//默认纵向写，若横向写需要加此项配置
        int length = list.length;
        if (length == 1) {
            excelWriter.fill(list[0], fillConfig, writeSheet);
        } else {
            // 如果有多个list 模板上必须有{前缀.} 这里的前缀就是 c0，然后多个list必须用 FillWrapper包裹
            for (int i = 0; i < length; i++) {
                excelWriter.fill(new FillWrapper("c" + i, list[i]), fillConfig, writeSheet);
            }
        }
        if (StrKit.notNull(map)) {
            excelWriter.fill(map, writeSheet);
        }
        excelWriter.finish();
    }

    //------------------------------------------------------------------------------------------
    //---------------------------------   Excel表格读取，同步读取  -----------------------------

    /**
     * @Param: [filePath 读取文件的绝对路径]
     * @return: 读取的结果集
     * @Author: ldl
     * @Create: 2020/06/28 028 11:11
     */
    @Remark("简单读取表格数据-同步")
    public static List<LinkedMap<String, String>> syncRead(String filePath) {
        //不指定用哪个class去读，然后读取第一个sheet 同步读取会自动finish
        BaseReadListener<Object> readListener = new BaseReadListener<>();
        //读取表格数据
        List<Map<Integer, String>> readedMapList = EasyExcel.read(filePath, readListener).sheet().doReadSync();
        //获取表格头，默认为第一行
        Map<Integer, String> headMap = readListener.getHeadMap();
        //按行封装数据并返回，表头为主要数据
        int headMapSize = headMap.size();//表头列数
        List<LinkedMap<String, String>> resultList = new ArrayList<>();
        LinkedMap<String, String> resultMap;
        for (Map<Integer, String> readedMap : readedMapList) {
            resultMap = new LinkedMap<>();
            for (int i = 0; i < headMapSize; i++) {
                resultMap.put(headMap.get(i), readedMap.get(i));
            }
            resultList.add(resultMap);
        }
        return resultList;
    }

    /**
     * @Param: [filePath 读取文件的绝对路径, clazz 指定用哪个类去读]
     * @return: 读取的结果集
     * @Author: ldl
     * @Create: 2020/06/28 028 11:11
     */
    @Remark("简单读取表格数据-同步")
    public static <T> List<T> syncRead(String filePath, Class<?> clazz) {
        //指定用哪个class去读，然后读取第一个sheet 同步读取会自动finish
        return EasyExcel.read(filePath).head(clazz).sheet().doReadSync();
    }

    //------------------------------------------------------------------------------------------
    //-------------------------  Excel表格异步读取，数据量大时推荐使用  ------------------------

    /**
     * @Param: [filePath 读取文件的绝对路径, clazz 指定用哪个类去读]
     * @return: 表格读取后的结果
     * @Author: ldl
     * @Create: 2020/06/28 028 10:01
     */
    @Remark("简单读取表格数据-异步")
    public static <T> ExcelReadResult<T> readExcel(String filePath, Class<?> clazz) {
        return readExcel(filePath, clazz, new BaseReadListener<>());
    }

    /**
     * @Param: [filePath 读取文件的绝对路径, clazz 指定用哪个类去读, readListener 自定义读取监听器]
     * @return: 表格读取后的结果
     * @Author: ldl
     * @Create: 2020/06/28 028 10:02
     */
    @Remark("指定监听器读取表格数据-异步")
    public static <T> ExcelReadResult<T> readExcel(String filePath, Class<?> clazz, BaseReadListener<T> readListener) {
        // 指定用哪个class去读，然后读取第一个sheet 文件流会自动关闭
        EasyExcel.read(filePath, clazz, readListener).sheet().doRead();
        // 回调方法，返回读取结果
        return readListener.getReadResult();
    }

}
